package common.toolkit.java.util.db;


import common.toolkit.java.constant.SymbolConstant;
import common.toolkit.java.entity.db.DBConnectionResource;
import common.toolkit.java.util.StringUtil;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;
import java.util.Properties;

/**
 * 数据库连接池管理,需要注意调用方法后是否需要释放相应资源。
 *
 * @author 银时 yinshi.nc@taobao.com
 */
public class DbcpUtil {

    private static Log LOG = LogFactory.getLog(DbcpUtil.class);

    private static BasicDataSource dataSource = null;

    public static String driverClassName = "com.mysql.jdbc.Driver";
    public static String dbJDBCUrl = "jdbc:mysql://10.232.31.154:3406/kenan";
    public static String characterEncoding = "UTF-8";
    public static String username = "yinshi";
    public static String password = "yinshi";
    public static int maxActive = 30;
    public static int maxIdle = 10;
    public static int maxWait = 10000;

    public DbcpUtil() {
    }

    public DbcpUtil(String driverClassName, String dbJDBCUrl, String characterEncoding, String username, String password, int maxActive, int maxIdle, int maxWait) {
        DbcpUtil.driverClassName = driverClassName;
        DbcpUtil.dbJDBCUrl = dbJDBCUrl;
        DbcpUtil.characterEncoding = characterEncoding;
        DbcpUtil.username = username;
        DbcpUtil.password = password;
        DbcpUtil.maxActive = maxActive;
        DbcpUtil.maxIdle = maxIdle;
        DbcpUtil.maxWait = maxWait;
    }


    /**
     * 初始化数据库连接
     */
    private static void init() throws Exception {

        /** 如果之前有连接池塘，关闭数据源  */
        shutdownDataSource();
        try {
            Properties p = new Properties();
            p.setProperty("driverClassName", driverClassName);
            if (StringUtil.containsIgnoreCase(dbJDBCUrl, SymbolConstant.QUESTION_SIGN)) {
                p.setProperty("url", dbJDBCUrl + SymbolConstant.AND_SIGN + "characterEncoding=" + characterEncoding);
            } else {
                p.setProperty("url", dbJDBCUrl + SymbolConstant.QUESTION_SIGN + "characterEncoding=" + characterEncoding);
            }
            p.setProperty("username", username);
            p.setProperty("password", password);
            p.setProperty("maxActive", String.valueOf(maxActive));
            p.setProperty("maxIdle", String.valueOf(maxIdle));
            p.setProperty("maxWait", String.valueOf(maxWait));
            LOG.warn("Start init datasource[driverName:" + driverClassName + ", url: " + p.getProperty("url") + ", username: [" + username + "], password: [" + password + "]");
            DbcpUtil.dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(p);
            LOG.warn("完成数据源创建，是否链接：" + !DbcpUtil.dataSource.isClosed());
        } catch (Exception e) {
            throw new Exception("创建数据源失败: " + e.getMessage(), e.getCause());
        }
    }

    /**
     * 关闭数据源
     */
    public static void shutdownDataSource() {
        if (null != DbcpUtil.dataSource) {
            try {
                DbcpUtil.dataSource.close();
            } catch (Exception e) {
                // ignore
            }
            DbcpUtil.dataSource = null;
        }
    }

    /**
     * 从连接池中获取数据库连接
     */
    private static synchronized Connection getConnection() throws Exception {

        if (null == DbcpUtil.dataSource) {
            init();
        }
        Connection conn = null;
        if (null != DbcpUtil.dataSource) {
            try {
                conn = dataSource.getConnection();
            } catch (Throwable e) {
                throw new Exception("Can't create conncetion, please make sure if database is available, " + e.getMessage(), e.getCause());
            }
        }
        return conn;
    }

    /**
     * 关闭结果集
     */
    public static void closeResultSet(ResultSet resultSet) {
        if (resultSet != null)
            try {
                resultSet.close();
            } catch (SQLException e) {
            }// IGNORE}
    }

    /**
     * 关闭结果集和Statement对象
     */
    public static void closeResultSetAndStatement(ResultSet resultSet, Statement statement) {
        if (resultSet != null)
            try {
                resultSet.close();
            } catch (SQLException e) {
            }// IGNORE}
        if (null != statement)
            try {
                statement.close();
            } catch (SQLException e) {
            }// IGNORE
    }

    /**
     * 关闭Statement对象
     */
    public static void closeStatement(Statement statement) {
        if (null != statement)
            try {
                statement.close();
            } catch (SQLException e) {
            }// IGNORE
    }

    /**
     * 归还连接
     */
    public static void returnBackConnectionToPool(Connection conn) {
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
            }// IGNORE
    }

    /**
     * 执行查询SQL, 注意，执行完这个方法必须执行： <br>
     * 1. DataSourceManager.closeResultSetAndStatement( resultSet, stmt ); <br>
     * 2. DataSourceManager.returnBackConnectionToPool( conn );
     *
     * @param querySql 查询SQL语句
     */
    public static DBConnectionResource executeQuery(String querySql) throws Exception {
        try {
            Connection conn = DbcpUtil.getConnection();
            if (null == conn) {
                throw new Exception("No available connection");
            }
            Statement stmt = conn.createStatement();
            return new DBConnectionResource(conn, stmt, stmt.executeQuery(querySql));
        } catch (Exception e) {
            throw new Exception("执行数据库查询[" + querySql + "]出错: " + e.getMessage(), e.getCause());
        }
    }

    /**
     * 执行插入SQL<br>
     * 此方法自己会释放资源，不需要调用方释放。
     */
    public static int executeInsert(String insertSql) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbcpUtil.getConnection();
            if (null == conn) {
                throw new Exception("No available connection");
            }
            stmt = conn.createStatement();
            return stmt.executeUpdate(insertSql);
        } catch (Exception e) {
            throw new Exception("Error when execute insert [" + insertSql + "],error: " + e.getMessage(), e.getCause());
        } finally {
            DbcpUtil.closeStatement(stmt);
            DbcpUtil.returnBackConnectionToPool(conn);
        }
    }

    /**
     * 执行插入SQL,并获取最后一次插入主键值
     * 此方法自己会释放资源，不需要调用方释放。
     */
    public static int executeInsertAndReturnGeneratedKeys(String insertSql) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DbcpUtil.getConnection();
            if (null == conn) {
                throw new Exception("No available connection");
            }
            stmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            rs = stmt.getGeneratedKeys();
            if (null != rs && rs.next()) {
                return rs.getInt(1);
            }
            return -1;
        } catch (Exception e) {
            throw new Exception("执行数据库插入[" + insertSql + "]出错: " + e.getMessage(), e.getCause());
        } finally {
            DbcpUtil.closeResultSetAndStatement(rs, stmt);
            DbcpUtil.returnBackConnectionToPool(conn);
        }
    }

    /**
     * 更新数据库 update
     * 此方法自己会释放资源，不需要调用方释放。
     */
    public static int executeUpdate(String updateSql) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbcpUtil.getConnection();
            if (null == conn) {
                throw new Exception("No available connection");
            }
            stmt = conn.createStatement();
            return stmt.executeUpdate(updateSql);
        } catch (Exception e) {
            throw new Exception("执行数据库更新[" + updateSql + "]出错: " + e.getMessage(), e.getCause());
        } finally {
            DbcpUtil.closeStatement(stmt);
            DbcpUtil.returnBackConnectionToPool(conn);
        }
    }

    /**
     * 删除
     * 此方法自己会释放资源，不需要调用方释放。
     */
    public static int executeDelete(String deleteSql) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbcpUtil.getConnection();
            if (null == conn) {
                throw new Exception("No available connection");
            }
            stmt = conn.createStatement();
            return stmt.executeUpdate(deleteSql);
        } catch (Exception e) {
            throw new Exception("执行数据库删除[" + deleteSql + "]出错: " + e.getMessage(), e.getCause());
        } finally {
            DbcpUtil.closeStatement(stmt);
            DbcpUtil.returnBackConnectionToPool(conn);
        }
    }

    public static void main(String[] args) {

        DBConnectionResource myResultSet = null;
        ResultSet resultSet = null;
        String querySql = "select * from biz_log_rule";
        try {
            myResultSet = DbcpUtil.executeQuery(querySql);
            resultSet = myResultSet.resultSet;
            System.out.println("Results:");
            int numcols = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= numcols; i++) {
                    System.out.print("\t" + resultSet.getString(i) + "\t");
                }
                System.out.println("");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != myResultSet)
                C3p0Util.closeResultSetAndStatement(resultSet, myResultSet.statement);
            C3p0Util.returnBackConnectionToPool(myResultSet.connection);
        }
    }


    public void setDataSource(BasicDataSource dataSource) {
        DbcpUtil.dataSource = dataSource;
    }

    public void setDriverClassName(String driverClassName) {
        DbcpUtil.driverClassName = driverClassName;
    }

    public void setDbJDBCUrl(String dbJDBCUrl) {
        DbcpUtil.dbJDBCUrl = dbJDBCUrl;
    }

    public void setUsername(String username) {
        DbcpUtil.username = username;
    }

    public void setPassword(String password) {
        DbcpUtil.password = password;
    }

    public void setMaxActive(int maxActive) {
        DbcpUtil.maxActive = maxActive;
    }

    public void setMaxIdle(int maxIdle) {
        DbcpUtil.maxIdle = maxIdle;
    }

    public void setMaxWait(int maxWait) {
        DbcpUtil.maxWait = maxWait;
    }

    public void setCharacterEncoding(String characterEncoding) {
        DbcpUtil.characterEncoding = characterEncoding;
    }

}
